﻿using CoreProject.DTO;
using CoreProject.IRepository;
using CoreProject.Model;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace CoreProject.Repository
{
    public class PderRepository:BaseRepository<Pder>,IPderRepository
    {
        private readonly EFDbContext _eFDbContext;
        public PderRepository(EFDbContext eFDbContext):base(eFDbContext) 
        {
            _eFDbContext = eFDbContext;
        }
        public async Task<List<Pder>> cha(Expression<Func<Pder, bool>> whereLambda)
        {
            return await _eFDbContext.Set<Pder>()
            .Include(u => u.User).Include(u => u.Pderxqs)
            .Where(whereLambda).ToListAsync();
        }
        public async Task<(List<Pder> list, int count)> chaf(int page, int size, string name,DateTime sj,DateTime sj1)
        {
            var pd = _eFDbContext.Pders.Where(n => n.Zt > 3 && n.Pdata >= sj1 && n.Pdata <= sj).Include(u => u.User).Include(n=>n.Pderxqs).AsQueryable();
           
                pd = pd.Where(u => u.User.UserName.Contains(name));
        

            int count = pd.Count();

            var list = await pd.OrderBy(u => u.Pbh).Skip((page - 1) * size).Take(size).ToListAsync();

            return (list, count);
        }

        public async Task<bool> gai(Pder d)
        {
            var zz = _eFDbContext.Pders.Where(n => n.Pbh == d.Pbh).FirstOrDefault();
            zz.Dbh = d.Dbh;
            zz.Zhifu = d.Zhifu;
            zz.Dizhi = d.Dizhi;
            zz.Pdata = d.Pdata;
            zz.Zt = d.Zt;
            zz.UserId = d.UserId;
            _eFDbContext.Pderxqs.RemoveRange(_eFDbContext.Pderxqs.Where(n => n.Pbh ==zz.Pbh).ToList());
            _eFDbContext.Pderxqs.AddRange(d.Pderxqs.ToList());
            _eFDbContext.Pders.Update(zz);
            return _eFDbContext.SaveChanges() > 0;
        }
        public async Task<bool>gai1(Pder d)
        {
            var zz=_eFDbContext.Pders.Where(n=>n.Pbh== d.Pbh).FirstOrDefault();
            zz.Zt=d.Zt;
            zz.Wcdata = DateTime.Now;
            _eFDbContext.Pders.Update(zz);
            return _eFDbContext.SaveChanges() > 0;
        }

        public async Task<bool> shan(string dd)
        {
            var zz = _eFDbContext.Pders.Where(n => n.Pbh == dd).FirstOrDefault();
            _eFDbContext.Pderxqs.RemoveRange(_eFDbContext.Pderxqs.Where(n=>n.Pbh==dd).ToList());

            _eFDbContext.Pders.Remove(zz);

            return _eFDbContext.SaveChanges()> 0;
        }

        public async Task<List<MonthStatistics1Dto>> GetMonthStatistics(string nian)
        {
            string sql = @"WITH Months AS (
                         SELECT 1 AS MonthNumber
                         UNION ALL
                         SELECT MonthNumber + 1
                         FROM Months
                         WHERE MonthNumber < 12
                         )
                  SELECT 
                       m.MonthNumber,
                       ISNULL(COUNT(DISTINCT t.dbh), 0) AS TotalAmount, 
                       ISNULL(SUM(xq.price * xq.shu), 0) AS JinAmount,
	                   ISNULL(SUM(xq.shu), 0) AS shuAmount
                   FROM 
                       Months m
                   LEFT JOIN 
                       (SELECT * FROM Pder WHERE YEAR(pdata) = @nian and zt>3) t 
                       ON MONTH(t.pdata) = m.MonthNumber
                   LEFT JOIN 
                      pderxq xq 
                      ON t.pbh = xq.pbh
	
                      GROUP BY 
                      m.MonthNumber
                      ORDER BY 
                      m.MonthNumber
                      OPTION (MAXRECURSION 0)";

            var parameters = new DynamicParameters();
            parameters.Add("@nian", nian);


            return await DapperHelper.QueryAsync<MonthStatistics1Dto>(sql, parameters);
        }





    }
}
